﻿Imports System.Data.SqlClient

Public Class frmVoucherReverse

    Sub FillVoucher()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select * From Transactions Where MoveNo=" & Me.txtMoveNo.Text & _
                                      " and Year(TransDate)=" & Me.DTPYear.Value.Year, cnn)
            Dim Reader As SqlDataReader

            Me.GridVoucher.Rows.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While (Reader.Read)
                Dim Row As String() = {Reader.Item("AccNo"), Reader.Item("Acc1"), Reader.Item("Acc2"), Reader.Item("Acc3"), _
                                       Reader.Item("Acc4"), Reader.Item("Descr"), Reader.Item("Branch"), _
                                       CDbl(Reader.Item("TotalOut")).ToString("N2"), CDbl(Reader.Item("TotalIn")).ToString("N2"), _
                                       Reader.Item("Source")}

                Me.GridVoucher.Rows.Add(Row)
                Me.DTPTrans.Value = CDate(Reader.Item("TransDate"))
                If CInt(Reader.Item("Reversed")) = 1 Then
                    Me.lblReverse.Text = "Reversed"
                End If
            End While
            cnn.Close()

            If Me.GridVoucher.Rows.Count > 0 Then
                Me.txtDescr1.Text = "Reversing voucher # " & Me.txtMoveNo.Text.Trim
            Else
                Me.txtDescr1.Text = "Reversing voucher # "
            End If

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Me.ErrorProvider1.Clear()
        If Me.GridVoucher.Rows.Count = 0 Then
            Me.ErrorProvider1.SetError(Me.txtMoveNo, "Please enter voucher no.")
        ElseIf Me.txtDescr.Text.Trim.Length = 0 Then
            Me.ErrorProvider1.SetError(Me.txtDescr, "Please enter transaction description")
        ElseIf ValidateDate(Me.DTPTrans.Value) = False Then
            Me.ErrorProvider1.SetError(Me.DTPTrans, "Date isn't accepted")
        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim MoveNo As Integer
                Dim StrIns, Time As String
                Dim cmd As New SqlCommand
                Dim Trans As SqlTransaction

                cmd.Connection = cnn

                cnn.Open()
                Trans = cnn.BeginTransaction
                cmd.Transaction = Trans

                'Get Move No.
                cmd.CommandText = "Select IsNull(Max(MoveNo),0) From Transactions Where Branch=N'" & Branch & "' and Year(TransDate)= " & Me.DTPTrans.Value.Year
                MoveNo = CInt(cmd.ExecuteScalar) + 1

                cmd.CommandText = "Select GetDate()"
                Time = CDate(cmd.ExecuteScalar).ToString("HH:mm:ss")

                For Each row As DataGridViewRow In Me.GridVoucher.Rows
                    StrIns = "Insert into Transactions (TransType,Descr,MoveNo,AccNo,Acc1,Acc2,Acc3,Acc4,Branch," & _
                             "TotalIn,TotalOut,SavedUser,TransDate) " & _
                             "Values (@TransType,@Descr,@MoveNo,@AccNo,@Acc1,@Acc2,@Acc3,@Acc4,@Branch," & _
                             "@TotalIn,@TotalOut,@SavedUser,@TransDate)"
                    cmd.CommandText = StrIns

                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("@TransType", "Journal Voucher")
                    cmd.Parameters.AddWithValue("@Descr", "Reversing voucher # " & Me.txtMoveNo.Text.Trim & " - " & Me.txtDescr.Text.Trim)
                    cmd.Parameters.AddWithValue("@MoveNo", MoveNo)
                    cmd.Parameters.AddWithValue("@AccNo", row.Cells(0).Value)
                    cmd.Parameters.AddWithValue("@Acc1", row.Cells(1).Value)
                    cmd.Parameters.AddWithValue("@Acc2", row.Cells(2).Value)
                    cmd.Parameters.AddWithValue("@Acc3", row.Cells(3).Value)
                    cmd.Parameters.AddWithValue("@Acc4", row.Cells(4).Value)
                    cmd.Parameters.AddWithValue("@Branch", row.Cells(6).Value)
                    cmd.Parameters.AddWithValue("@TotalOut", CDbl(row.Cells(8).Value))
                    cmd.Parameters.AddWithValue("@TotalIn", CDbl(row.Cells(7).Value))
                    cmd.Parameters.AddWithValue("@TransDate", Me.DTPTrans.Value.ToString("MM/dd/yyyy") & " " & Time)
                    cmd.Parameters.AddWithValue("@SavedUser", CurrentUser)
                    cmd.ExecuteNonQuery()
                Next

                'Update Reserved=1 flag 
                cmd.Parameters.Clear()
                cmd.CommandText = "Update Transactions Set Reversed=1 Where Year(TransDate)= " & Me.DTPYear.Value.Year & _
                                  " and MoveNo=" & Me.txtMoveNo.Text.Trim
                cmd.ExecuteNonQuery()

                Trans.Commit()
                cnn.Close()

                MsgBox("Saved Successfully")

                Clear()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                MsgBox(ex.ToString)
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
            End Try
        End If
    End Sub

    Sub Clear()
        Me.txtMoveNo.Clear()
        Me.GridVoucher.Rows.Clear()
        Me.txtDescr.Clear()
        Me.DTPTrans.Value = Date.Today
        Me.lblReverse.Text = ""
        Me.txtDescr1.Text = "Reversing voucher # "
    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.GridVoucher.Rows.Clear()
        Me.DTPTrans.Value = Date.Today
        Me.lblReverse.Text = ""
        Me.txtDescr1.Text = "Reversing voucher # "
    End Sub

    Private Sub txtMoveNo_KeyUp1(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtMoveNo.KeyUp
        If e.KeyCode = Keys.Enter Then
            Me.ErrorProvider1.Clear()

            If Len(Me.txtMoveNo.Text) = 0 Then
                Me.ErrorProvider1.SetError(Me.txtMoveNo, "Please enter voucher no.")
                Me.txtMoveNo.Focus()
                Exit Sub
            Else
                FillVoucher()
            End If
        End If
    End Sub

    Private Sub txtMoveNo_TextChanged_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtMoveNo.TextChanged
        Me.GridVoucher.Rows.Clear()
        Me.DTPTrans.Value = Date.Today
        Me.lblReverse.Text = ""
        Me.txtDescr1.Text = "Reversing voucher # "
    End Sub

    Private Sub btnPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.ErrorProvider1.Clear()

        If Len(Me.txtMoveNo.Text) = 0 Then
            Me.ErrorProvider1.SetError(Me.txtMoveNo, "Please enter voucher no.")
            Me.txtMoveNo.Focus()
            Exit Sub
        Else
            Me.Cursor = Cursors.WaitCursor
            FillVoucher()
            Me.Cursor = Cursors.Default
        End If
    End Sub

    Private Sub ChAccPath_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChAccPath.CheckedChanged
        If Me.ChAccPath.Checked = True Then
            Me.Acc1.Visible = True
            Me.Acc2.Visible = True
            Me.Acc3.Visible = True
        ElseIf Me.ChAccPath.Checked = False Then
            Me.Acc1.Visible = False
            Me.Acc2.Visible = False
            Me.Acc3.Visible = False
        End If
    End Sub

    Private Sub DateTimePicker1_ValueChanged(sender As System.Object, e As System.EventArgs) Handles DTPYear.ValueChanged
        Me.GridVoucher.Rows.Clear()
        Me.DTPTrans.Value = Date.Today
        Me.lblReverse.Text = ""
        Me.txtDescr1.Text = "Reversing voucher # "
    End Sub

    Private Sub frmVoucherReverse_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized
    End Sub
End Class